package db;

import beans.UseOrder;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class UseOrderDao extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public UseOrderDao() {
        connection = new MySQLConnectionBean();
    }

    // Insert a new use order
    public boolean insertUseOrder(UseOrder order) throws SQLException {
        String sql = "INSERT INTO use_order (order_id, EmployeeID, DriverID, expected_departure_time, " +
                "expected_return_time, remarks, VehicleID, usage_reason, passengers, review_status, reviewer, purpose, Destination, statusremarks) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, order.getOrderId());
            statement.setInt(2, order.getEmployeeId());
            statement.setInt(3, order.getDriverId());
            statement.setTimestamp(4, new Timestamp(order.getExpectedDepartureTime().getTime()));
            statement.setTimestamp(5, new Timestamp(order.getExpectedReturnTime().getTime()));
            statement.setString(6, order.getRemarks());
            statement.setInt(7, order.getVehicleId());
            statement.setString(8, order.getUsageReason());
            statement.setString(9, order.getPassengers());
            statement.setString(10, order.getReviewStatus());
            if (order.getReviewer() != null) {
                statement.setInt(11, order.getReviewer());
            } else {
                statement.setNull(11, Types.INTEGER);
            }
            statement.setString(12, order.getPurpose());
            statement.setString(13, order.getDestination());
            statement.setString(14, order.getStatusremarks());
            return statement.executeUpdate() > 0;
        }
    }

    //搜索
    public List<UseOrder> searchOrders(String orderId, Integer employeeId,
                                       String status, java.util.Date startDate, Date endDate,int m) {
        List<UseOrder> orders = new ArrayList<>();
        String SQL;
        if(m==1) SQL="SELECT * FROM use_order WHERE review_status != '待审核'";
        else SQL="SELECT * FROM use_order WHERE review_status = '待审核'";
        StringBuilder sql = new StringBuilder(SQL);
        List<Object> params = new ArrayList<>();

        if (orderId != null && !orderId.isEmpty()) {
            sql.append(" AND order_id = ?");
            params.add(orderId);
        }
        if (employeeId != null) {
            sql.append(" AND EmployeeID = ?");
            params.add(employeeId);
        }
        if (status != null && !status.isEmpty()) {
            sql.append(" AND review_status = ?");
            params.add(status);
        }
        if (startDate != null) {
            sql.append(" AND expected_departure_time >= ?");
            params.add(new java.sql.Timestamp(startDate.getTime()));
        }
        if (endDate != null) {
            sql.append(" AND expected_departure_time <= ?");
            params.add(new java.sql.Timestamp(endDate.getTime()));
        }

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                Object param = params.get(i);
                if (param instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) param);
                } else if (param instanceof java.sql.Timestamp) {
                    pstmt.setTimestamp(i + 1, (java.sql.Timestamp) param);
                } else {
                    pstmt.setString(i + 1, param.toString());
                }
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                UseOrder order = extractDriverFromResultSet(rs);
                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }
// 在UseOrderDao.java中添加以下方法

    // 根据员工ID获取订单
    public List<UseOrder> getOrdersByEmployeeId(int employeeId) throws SQLException {
        List<UseOrder> orders = new ArrayList<>();
        String sql = "SELECT * FROM use_order WHERE EmployeeID=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setInt(1, employeeId);
            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    UseOrder order = extractDriverFromResultSet(rs);
                    orders.add(order);
                }
            }
        }
        return orders;
    }
    // 在UseOrderDao.java中添加以下方法

    // 根据订单号和员工ID获取订单（确保员工只能查询自己的订单）
    public UseOrder getOrderByIdAndEmployeeId(String orderId, int employeeId) throws SQLException {
        String sql = "SELECT * FROM use_order WHERE order_id = ? AND EmployeeID = ?";
        UseOrder order = null;

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, orderId);
            statement.setInt(2, employeeId);

            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    order = extractDriverFromResultSet(rs);
                }
            }
        }
        return order;
    }

    // 获取员工的最新订单
    public UseOrder getLatestOrderByEmployeeId(int employeeId) throws SQLException {
        String sql = "SELECT * FROM use_order WHERE EmployeeID = ? ORDER BY order_id DESC LIMIT 1";
        UseOrder order = null;

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setInt(1, employeeId);

            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    order = extractDriverFromResultSet(rs);
                }
            }
        }
        return order;
    }
    // 在UseOrderDao.java中添加以下方法

    // 检查订单是否存在
    public boolean orderExists(String orderId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM use_order WHERE order_id = ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, orderId);

            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    // 根据订单号获取订单（不限制员工ID）
    public UseOrder getOrderById(String orderId) throws SQLException {
        String sql = "SELECT * FROM use_order WHERE order_id = ?";
        UseOrder order = null;

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, orderId);

            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    order = extractDriverFromResultSet(rs);
                }
            }
        }
        return order;
    }
    public List<UseOrder> findByStatusNot(String excludedStatus) {
        List<UseOrder> orders = new ArrayList<>();
        System.out.println("1111111111DAO");
        String sql = "SELECT * FROM use_order WHERE review_status != ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, excludedStatus);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                UseOrder order = extractDriverFromResultSet(rs);
                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("orders.size="+orders.size());
        return orders;
    }

    public List<UseOrder> findByStatus(String excludedStatus) {
        List<UseOrder> orders = new ArrayList<>();
        System.out.println("DAO。11");
        String sql = "SELECT * FROM use_order WHERE review_status = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, excludedStatus);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                UseOrder order = extractDriverFromResultSet(rs);
                orders.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
    }
    private UseOrder extractDriverFromResultSet(ResultSet rs) throws SQLException {
        UseOrder order = new UseOrder();
        order.setOrderId(rs.getString("order_id"));
        order.setEmployeeId(rs.getInt("EmployeeID"));
        order.setDriverId(rs.getInt("DriverID"));
        order.setExpectedDepartureTime(rs.getTimestamp("expected_departure_time"));
        order.setExpectedReturnTime(rs.getTimestamp("expected_return_time"));
        order.setRemarks(rs.getString("remarks"));
        order.setVehicleId(rs.getInt("VehicleID"));
        order.setUsageReason(rs.getString("usage_reason"));
        order.setPassengers(rs.getString("passengers"));
        order.setReviewStatus(rs.getString("review_status"));
        order.setReviewer(rs.getInt("reviewer"));
        order.setPurpose(rs.getString("purpose"));
        order.setDestination(rs.getString("Destination"));
        order.setStatusremarks(rs.getString("statusremarks"));
        return order;
    }

    // Get all use orders
    public List<UseOrder> getAllUseOrders() throws SQLException {
        List<UseOrder> orders = new ArrayList<>();
        String sql = "SELECT * FROM use_order";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql);
             ResultSet rs = statement.executeQuery()) {

            while (rs.next()) {
                UseOrder order = extractDriverFromResultSet(rs);
                orders.add(order);
            }
        }
        return orders;
    }

    // Update a use order
    public boolean updateUseOrder(UseOrder order) throws SQLException {
        String sql = "UPDATE use_order SET EmployeeID=?, DriverID=?, expected_departure_time=?, " +
                "expected_return_time=?, remarks=?, VehicleID=?, usage_reason=?, passengers=?, " +
                "review_status=?, reviewer=?, purpose=?, Destination=?, statusremarks=? WHERE order_id=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setInt(1, order.getEmployeeId());
            statement.setInt(2, order.getDriverId());
            statement.setTimestamp(3, new Timestamp(order.getExpectedDepartureTime().getTime()));
            statement.setTimestamp(4, new Timestamp(order.getExpectedReturnTime().getTime()));
            statement.setString(5, order.getRemarks());
            statement.setInt(6, order.getVehicleId());
            statement.setString(7, order.getUsageReason());
            statement.setString(8, order.getPassengers());
            statement.setString(9, order.getReviewStatus());
            if (order.getReviewer() != null) {
                statement.setInt(10, order.getReviewer());
            } else {
                statement.setNull(10, Types.INTEGER);
            }
            statement.setString(11, order.getPurpose());
            statement.setString(12, order.getDestination());
            statement.setString(13, order.getStatusremarks());
            statement.setString(14, order.getOrderId());

            return statement.executeUpdate() > 0;
        }
    }

    // Delete a use order
    public boolean deleteUseOrder(String orderId) throws SQLException {
        String sql = "DELETE FROM use_order WHERE order_id=?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, orderId);
            return statement.executeUpdate() > 0;
        }
    }

    // Get use order by ID
    public UseOrder getUseOrderById(String orderId) throws SQLException {
        String sql = "SELECT * FROM use_order WHERE order_id=?";
        UseOrder order = null;

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            statement.setString(1, orderId);
            try (ResultSet rs = statement.executeQuery()) {
                if (rs.next()) {
                    order = new UseOrder();
                    order.setOrderId(rs.getString("order_id"));
                    order.setEmployeeId(rs.getInt("EmployeeID"));
                    order.setDriverId(rs.getInt("DriverID"));
                    order.setExpectedDepartureTime(rs.getTimestamp("expected_departure_time"));
                    order.setExpectedReturnTime(rs.getTimestamp("expected_return_time"));
                    order.setRemarks(rs.getString("remarks"));
                    order.setVehicleId(rs.getInt("VehicleID"));
                    order.setUsageReason(rs.getString("usage_reason"));
                    order.setPassengers(rs.getString("passengers"));
                    order.setReviewStatus(rs.getString("review_status"));
                    int reviewer = rs.getInt("reviewer");
                    if (!rs.wasNull()) {
                        order.setReviewer(reviewer);
                    } else {
                        order.setReviewer(null);
                    }
                    order.setPurpose(rs.getString("purpose"));
                    order.setDestination(rs.getString("Destination"));
                    order.setStatusremarks(rs.getString("statusremarks"));
                }
            }
        }
        return order;
    }

    // 在UseOrderDao.java中添加的搜索方法
    public List<UseOrder> searchUseOrders(String keyword) throws SQLException {
        List<UseOrder> orders = new ArrayList<>();
        String sql = "SELECT * FROM use_order WHERE order_id LIKE ? OR usage_reason LIKE ? OR remarks LIKE ?";

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql)) {

            String searchPattern = "%" + keyword + "%";
            statement.setString(1, searchPattern);
            statement.setString(2, searchPattern);
            statement.setString(3, searchPattern);

            try (ResultSet rs = statement.executeQuery()) {
                while (rs.next()) {
                    UseOrder order = new UseOrder();
                    order.setOrderId(rs.getString("order_id"));
                    order.setEmployeeId(rs.getInt("EmployeeID"));
                    order.setDriverId(rs.getInt("DriverID"));
                    order.setExpectedDepartureTime(rs.getTimestamp("expected_departure_time"));
                    order.setExpectedReturnTime(rs.getTimestamp("expected_return_time"));
                    order.setRemarks(rs.getString("remarks"));
                    order.setVehicleId(rs.getInt("VehicleID"));
                    order.setUsageReason(rs.getString("usage_reason"));
                    order.setPassengers(rs.getString("passengers"));
                    order.setReviewStatus(rs.getString("review_status"));
                    order.setReviewer(rs.getInt("reviewer"));
                    order.setPurpose(rs.getString("purpose"));
                    order.setDestination(rs.getString("Destination"));
                    if (rs.wasNull()) {
                        order.setReviewer(null);
                    }
                    orders.add(order);
                }
            }
        }
        return orders;
    }

    public UseOrder getOrderById(int orderId) throws SQLException {
        String sql = "SELECT * FROM use_order WHERE order_id = ?";
        try (Connection conn = getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, String.valueOf(orderId));  // 因为order_id是varchar类型
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    UseOrder order = new UseOrder();
                    order.setOrderId(rs.getString("order_id"));
                    order.setEmployeeId(rs.getInt("EmployeeID"));
                    order.setDriverId(rs.getInt("DriverID"));
                    order.setExpectedDepartureTime(rs.getTimestamp("expected_departure_time"));
                    order.setExpectedReturnTime(rs.getTimestamp("expected_return_time"));
                    order.setRemarks(rs.getString("remarks"));
                    order.setVehicleId(rs.getInt("VehicleID"));
                    order.setUsageReason(rs.getString("usage_reason"));
                    order.setPassengers(rs.getString("passengers"));
                    order.setReviewStatus(rs.getString("review_status"));
                    order.setReviewer(rs.getInt("reviewer"));
                    order.setPurpose(rs.getString("purpose"));
                    order.setDestination(rs.getString("Destination"));
                    order.setStatusremarks(rs.getString("statusremarks"));
                    if (rs.wasNull()) {
                        order.setReviewer(null);
                    }
                    return order;
                }
            }
        }
        return null;
    }

    public boolean updateOrderStatus(String orderId, String status) throws SQLException {
        String sql = "UPDATE use_order SET review_status = ?, statusremarks = ? WHERE order_id = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, status);
            pstmt.setString(2, "司机已确认完成订单");
            pstmt.setString(3, orderId);
            return pstmt.executeUpdate() > 0;
        }
    }

    /**
     * 将用车订单数据插入到 use_order 表，根据申请信息创建，处理int字段为0时插入NULL
     * @param useOrder 包含用车订单数据的 UseOrder 对象
     * @return boolean 插入成功返回 true，否则返回 false
     */
    public boolean insertUseOrderByShenqing(UseOrder useOrder) {
        String sql = "INSERT INTO use_order (order_id, EmployeeID, DriverID, expected_departure_time, " +
                "expected_return_time, remarks, VehicleID, usage_reason, passengers, review_status, " +
                "reviewer, purpose, Destination, statusremarks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        try {
            conn = connection.getConnection();
            conn.setAutoCommit(false); // 开启事务

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                // 绑定参数
                pstmt.setString(1, useOrder.getOrderId());
                pstmt.setInt(2, useOrder.getEmployeeId());
                pstmt.setInt(3, useOrder.getDriverId());
                pstmt.setTimestamp(4, new Timestamp(useOrder.getExpectedDepartureTime().getTime()));
                pstmt.setTimestamp(5, new Timestamp(useOrder.getExpectedReturnTime().getTime()));
                pstmt.setString(6, useOrder.getRemarks());
                pstmt.setInt(7, useOrder.getVehicleId());
                pstmt.setString(8, useOrder.getUsageReason());
                pstmt.setString(9, useOrder.getPassengers());
                pstmt.setString(10, useOrder.getReviewStatus());
                pstmt.setInt(11, useOrder.getReviewer());
                pstmt.setString(12, useOrder.getPurpose());
                pstmt.setString(13, useOrder.getDestination());
                pstmt.setString(14, useOrder.getStatusremarks());

                int rowsAffected = pstmt.executeUpdate();
                if (rowsAffected > 0) {
                    conn.commit(); // 提交事务
                    return true;
                } else {
                    conn.rollback(); // 回滚事务
                    return false;
                }
            }
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 发生异常时回滚事务
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}